##################################################################################################################################
#
# Copyright (C) 2008-2013 Mind System Connection. Lda. Part of Daidze Software, created by Siqsuruq. All other rights reserved.
#
# Mind System Connection Webpage: www.msysc.org
# Daidze Software Page: www.daidze.com www.daidze.org
# E-mail: admin@msysc.org, siqsuruq@gmail.com
#
##################################################################################################################################

# Start of class definition

oo::class create my_sqlitedb {
	constructor {db_name} {
		package require sqlite3
		my variable conn
		set conn [sqlite3 [namespace current]::conn $db_name]
	}

	
	method show_tables {} {
		my variable conn
		set query "SELECT tbl_name FROM sqlite_master WHERE type = \"table\""
		return [conn eval $query]
	}
	
	method show_columns {tbl} {
		my variable conn
		set query "PRAGMA table_info($tbl)"
		return [conn eval $query]
	}
	
	method table_exist {table_name} {
		my variable conn
		set query "SELECT name FROM sqlite_master WHERE type=\"table\" AND name=\"$table_name\""
		return [conn eval $query]
	}
	
	method disconnect {} {
		my variable conn
		conn close 
	}
##############################################################################################################
	# method Save_query_to_log {query} {
		# if {[info exists ::debug] != 0 && $::debug == "true"} {
			# .find.info delete 1.0 end
			# .find.info insert end "WEVE TRIED THIS QUERY: $query"
		# }
	# }

	method create_table {tbl args} {
		if {[llength $args] < 2} {
			set tmp [list]
			foreach key [dict keys [lindex $args 0]] val [dict values [lindex $args 0]] {
				lappend tmp "$key $val"
			}
			set comma_sep [join $tmp ", "]
		}
		set query "CREATE TABLE IF NOT EXISTS $tbl ($comma_sep)"
		my execute_query $query
	}
	
	method insert {tbl values} {
		my variable conn
		set my_columns [dict keys $values]
		set my_values [list]
	
		set vals [dict values $values]
		foreach val $vals {
			lappend my_values [pg_quote $val]
		}

		set query "INSERT INTO $tbl ([::csv::join $my_columns]) VALUES ([::csv::join $my_values , ""])"
		my execute_query $query
	}
	


	
	
	method get_columns_types {table {columns "*"}} {
		my variable conn
		set col_type_list [list]
		set query "PRAGMA table_info($table)"
		set result [conn eval $query]
		if {$columns eq "*"} {
			for {set pos 2} {$pos < [llength $result]} {incr pos 6} {
				lappend col_type_list [lindex $result $pos]
			}
		} else {
			for {set pos 2} {$pos < [llength $result]} {incr pos 6} {
				if {[lsearch -exact [lindex $result [expr $pos - 1]] $columns] != -1 } {
					lappend col_type_list [lindex $result $pos]
				}
			}
		}
		return $col_type_list
	}
	
	
	



	method get_server_info {} {
		my variable conn
		return [conn version]
	}
############################################################################################################################

########################################################## Select ##########################################################
	method select_last_inserted {table column} {
		my variable conn
		set query "SELECT $column FROM \"$table\" ORDER BY $column DESC LIMIT 1"
		return [conn eval $query]
	} 

	method transaction {status} {

	}
	
	method select_columns_names {table} {
		my variable conn
		set tbl_info [conn eval "PRAGMA table_info($table)"]
		set columns_list [list]
		for {set x 1} {$x < [llength $tbl_info]} {incr x 6} {
			lappend columns_list [lindex $tbl_info $x]
		}
		return $columns_list
	}

	method select_column {table column} {
		my variable conn   
		set query "SELECT $column FROM $table"
		return [conn eval $query]
	}

	method select_id_by_name {table name} {
		my variable conn   
		set query "SELECT id FROM $table WHERE name='$name'"
		return [conn eval $query]
	}

	method select_name_by_id {table id} {
		my variable conn   
		set query "SELECT name FROM $table WHERE id='$id'"
		return [conn eval $query]
	}


	method select_all_between_dates {table date_column init_date end_date} {

	}

	method select_all {table {columns "*"} {extra "none"} {res_type "dict"}} {
		my variable conn
		if {$columns == "*"} {
			set columns [my select_columns_names $table]
		}


		set my_columns [list]
		set my_tables [list $table]
		set fklist [list]

		foreach col $columns {
			if {[string match fk_* $col] == 1} {
				lappend my_tables [set fk_table [::textutil::trim::trim $col fk_]]
				lappend my_columns "$fk_table.name AS $fk_table\_name"
				lappend fklist " $table.$col=$fk_table.id "
			} else {
				lappend my_columns "$table.$col AS $col"
			}
		}

		set query "SELECT "
		append query "[::csv::join $my_columns]"
		append query " FROM [::csv::join $my_tables] "
		if {[llength $fklist] != 0 && $extra != "none"} {
			append query " WHERE "
			append query [::csv::join $fklist AND]
			append query " AND $extra"
		} elseif {[llength $fklist] != 0 && $extra == "none"} {
			append query " WHERE "
			append query [::csv::join $fklist AND]
		} elseif {[llength $fklist] == 0 && $extra != "none"} {
			append query " WHERE "
			append query " $extra"
		}
		set line_count 0
		set result [dict create]
		puts $query
		conn eval $query values {
			set line [dict create]
			foreach key $values(*) {
				set key_val_par [array get values $key]
				dict append line [lindex $key_val_par 0] [lindex $key_val_par 1]
			}
			dict append result $line_count $line
			incr line_count
		}

		if {$res_type == "llist" || $res_type == "list" } {
			set res [my convert_dict_to_simplelist $result]
		} elseif {$res_type == "dict"} {
			set res $result
		}
		return $res
	}

############################################################################################################################

########################################################## Insert ##########################################################

	method insert_all {table data} {
		my variable conn

		set my_columns [list]
		set my_values [list]

		foreach col [dict keys $data] {
			lappend my_columns $col
			if {[string match fk_* $col] == 1} {
				if {[dict get $data $col] != ""} {
					lappend my_values [pg_quote [my select_id_by_name [::textutil::trim::trim $col fk_] [dict get $data $col]]]
				} else {
					lappend my_values NULL
				}

			} else {
				if {[dict get $data $col] != ""} {
					lappend my_values [pg_quote [dict get $data $col]]
				} else {
					lappend my_values NULL
				}
			}
		}

		set query "INSERT INTO \"$table\" ([::csv::join $my_columns]) VALUES ([::csv::join $my_values , ""])"
		puts "\n $query \n"
		my execute_query $query
	}



############################################################################################################################

########################################################## Update ##########################################################
	method update_all {table data} {
		my variable conn
		set my_values [list]

		foreach col [dict keys $data] val [dict values $data] {
			if {[string match fk_* $col] == 1} {
				lappend my_values $col=[pg_quote [my select_id_by_name [::textutil::trim::trim $col fk_] $val]]
			} else {
				if {$val != ""} {
					lappend my_values $col=[pg_quote $val]
				} else {
					lappend my_values $col=NULL
				}
			}
		}
		set query "UPDATE $table SET [::csv::join $my_values , ""] WHERE [lindex $my_values 0]"
		my execute_query $query
	}

############################################################################################################################

########################################################## Delete ##########################################################
	method delete_row {table row} {
		my variable conn    
		set query "DELETE FROM $table WHERE id='$row'"
		my execute_query $query
	}

	method delete_rows_in_relative {table related_col related_val} {
		my variable conn    
		set query "DELETE FROM $table WHERE $related_col='$related_val'"
		my execute_query $query
	}
	
	method reset_id {table} {
		my variable conn
		set reset_query "DELETE FROM sqlite_sequence WHERE name='$table'"
		my execute_query $reset_query
	}

	method delete_all_reset_id {table} {
		my variable conn
		set empty_query "DELETE FROM $table"
		my execute_query $empty_query
		my reset_id $table
	}

############################################################################################################################

########################################################## General #########################################################

	method execute_query {query {res "list"}} {
		my variable conn
		set line_count 0
		set result [dict create]
		conn eval "$query" values {
			set line [dict create]
			foreach key $values(*) {
				set key_val_par [array get values $key]
				dict append line [lindex $key_val_par 0] [lindex $key_val_par 1]
			}
			dict append result $line_count $line
			incr line_count
		}
		if {$res eq "dict"} {
			set final_result $result
		} elseif {$res eq "list" || $res eq "llist"} {
			set final_result [my convert_dict_to_list $result]
		}
		return $final_result
	} 

	method execute_bin {col table id} {

	} 
	
	
	method llist_to_dict {vllist columnslist} {

	}
	
	method convert_dict_to_list {data_dict} {
		set data_list [list]
		foreach key [dict keys $data_dict] {
			lappend data_list [dict values [dict get $data_dict $key]]
		}
		return $data_list
	}
	
	method convert_dict_to_simplelist {data_dict} {
		set data_list [list]
		foreach line_num [dict keys $data_dict] {
			set line [dict get $data_dict $line_num]
			foreach key [dict keys $line] {
				lappend data_list [dict get $line $key]
			}
		}
		return $data_list
	}

	method select_all_case {table data args} {

	}

}


proc arrayval {array {pattern *}} {
	upvar $array a
	if { ![array exists a] } { error "\"$array\" isn't an array" }
		set lines [list]
		set max 0
		foreach name [array names a $pattern] {
			set len [string length $name]
			if { $len > $max } { set max $len }
		}
		set max [expr {$max + [string length $array] + 2}]
		foreach name [array names a $pattern] {
			set line [format %s(%s) $array $name]
			lappend lines [format "%-*s = %s" $max $line $a($name)]
		}
}